package cn.edu.cdut.service;

import cn.edu.cdut.dao.ScoreInfoDao;
import cn.edu.cdut.model.ScoreInfo;
import cn.edu.cdut.utils.ReadExcelUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.ResourceUtils;

import javax.annotation.Resource;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;

/**
 * Author  kobe
 * Created on 2017-05-28-21:44
 */
@Service
public class ScoreInfoService {

        @Autowired
        private ScoreInfoDao scoreInfoDao;

        //读取学生所有课的成绩信息
        @Transactional
        public void readScoreInfo(String filename) throws IOException {
                //删除之前信息
                scoreInfoDao.deleteAll();

                //读取
                File scoreFile= ResourceUtils.getFile("src/main/resources/static/" + filename);
                Workbook wb;//Excel工作簿
                Sheet sheet;
                String suff = ReadExcelUtil.parseFileSuffix(scoreFile);
                if (suff.equals("xls")) {
                        wb = new HSSFWorkbook(new FileInputStream(scoreFile));
                        sheet = wb.getSheetAt(0);
                        readScoreInfo2DB(sheet);
                } else if (suff.equals("xlsx")) {
                        wb= new XSSFWorkbook(new FileInputStream(scoreFile));
                        sheet = wb.getSheetAt(0);
                        readScoreInfo2DB(sheet);
                }
        }
        private void readScoreInfo2DB(Sheet sheet ) {

                //sheet.getLastRowNum()
                for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
                        Row row = sheet.getRow(i);
                        ScoreInfo scoreInfo = new ScoreInfo();
                        scoreInfo.setClassId(row.getCell(0).getStringCellValue());
                        scoreInfo.setStuId(row.getCell(1).getStringCellValue());
                        scoreInfo.setStuName(row.getCell(2).getStringCellValue());
                        scoreInfo.setCourse(row.getCell(3).getStringCellValue());
                        scoreInfo.setCredit(Double.parseDouble(row.getCell(4).getStringCellValue()));
                        scoreInfo.setScore(row.getCell(5).getStringCellValue());
                        scoreInfo.setPoint(Double.parseDouble(row.getCell(6).getStringCellValue()));
                        scoreInfo.setStuSubject(row.getCell(7).getStringCellValue());
                        scoreInfoDao.save(scoreInfo);
                }
        }
}
